﻿use Workflow
go
--创建团队属性表
create table Teams
(
	TeamId int identity(0,1) primary key,
	TeamName nvarchar(50) not null
)
go
--创建角色属性表
create table Roles
(
	RoleId int identity(0,1) primary key,
	RoleName nvarchar(50) not null,
	BelongTeam int references Teams(TeamId) not null
)
go
--创建用户信息表
create table UserInfoes
(
	UserName nvarchar(50) not null primary key,
	DisplayName nvarchar(50) not null,
	Email nvarchar(50)
)
go
--创建工作流模板表
create table WorkflowMoulds
(
	WorkParentId int identity(1,1) primary key,
	WorkName nvarchar(50) not null,
	NodeCount int not null,--节点数量
	CreateTeamId int references Teams(TeamId),
	CreateRoleId int references Roles(RoleId),
	TableName nvarchar(20),
	ReaderTeamId int references Teams(TeamId),
	ReaderRoleId int references Roles(RoleId),
	WebName nvarchar(50)
)
go
--创建用户团队角色关系表
create table UserTeamRoles
(
	Id int identity(1,1) primary key,
	UserName nvarchar(50) references UserInfoes(UserName),
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	WorkMouldId int references WorkflowMoulds(WorkParentId)
)
go

--创建工作流子节点模板表
create table WorkflowChildMoulds
(
	WorkChildId int identity(1,1) primary key,
	WorkParentId int references WorkflowMoulds(WorkParentId),
	NodeNo int not null,--节点序号
	IsSingleNode int not null,
	ChildNodeNo int not null,--子节点序号
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	UserName nvarchar(50) references UserInfoes(UserName),
	NodeName nvarchar(50) 
)
use Workflow
go
--创建工作流状态表
create table WorkflowStatuses
(
	WorkParentId int identity(1,1) primary key,
	CreaterUserName nvarchar(50) references UserInfoes(UserName),
	WorkMouldId int references WorkflowMoulds(WorkParentId),
	WorkName nvarchar(50) not null,
	NodeCount int not null,
	StatusDefine int default(0) not null,
	StatusNo int default(0) not null,
	CreateTime datetime default(getdate()),
	CompleteTime datetime,
	CreaterTeamId int references Teams(TeamId),
	CreaterRoleId int references Roles(RoleId),
	ReaderTeamId int references Teams(TeamId),
	ReaderRoleId int references Roles(RoleId),
	TableName nvarchar(20),
	WebName nvarchar(50)
)
--创建工作流子节点状态表
create table WorkflowChildStatuses
(
	WorkChildId int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	NodeNo int not null,--节点序号
	IsSingleNode int not null,
	ChildNodeNo int not null,--子节点序号
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	StatusNo int not null,
	LastChangeTime datetime,
	UserName nvarchar(50) references UserInfoes(UserName),
	NodeName nvarchar(50) 
)
go
use Workflow
--创建工作流状态Log日志表
create table WorkflowLog
(
	Id int identity(1,1) primary key,
	UserName nvarchar(50) references UserInfoes(UserName),
	WorkParentId int references WorkflowStatuses(WorkParentId),
	WorkMouldId int references WorkflowMoulds(WorkParentId),
	WorkChildId int references WorkflowChildStatuses(WorkChildId),
	StatusNo int not null,
	ChildNodeNo int not null,
	ChangeTime datetime not null,
	Type1 nvarchar(10) not null 
)

go
use Workflow
--创建上传文件信息表
create table WorkflowUpLoad
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	UpLoadTime datetime default(getdate()),
	FileAddress nvarchar(max)
)

use Workflow
go
create table BaoJia
(
	Id int identity(1,1) primary key,
	WorkParentId int,
	Number nvarchar(50),
	Customer nvarchar(50),
	Class nvarchar(50),
	CostType nvarchar(50),
	Requirement nvarchar(50),
	Sales nvarchar(50),
	Success nvarchar(1),
	MoldRequire nvarchar(50),
	MoldClass nvarchar(50)
)
use Workflow
go
create table BaoJiaLog
(
	Id int identity(1,1) primary key,
	DisplayName nvarchar(50),
	ChangeTime datetime default(getdate()),
	WorkParentId int,
	Number nvarchar(50),
	Customer nvarchar(50),
	Class nvarchar(50),
	CostType nvarchar(50),
	Requirement nvarchar(50),
	Sales nvarchar(50),
	Success nvarchar(1),
	MoldRequire nvarchar(50),
	MoldClass nvarchar(50)
)
use Workflow
go
create table GetNum
(
	Id int identity(1,1) primary key,
	WorkParentId int,
	Business nvarchar(20),
	ZNum nvarchar(20),
	SapMold nvarchar(20),
	SapProject nvarchar(20),
	OrderNumber nvarchar(50),
	CostCenter nvarchar(20)
)
use Workflow
go
create table GetNumMould
(
	Business nvarchar(20) primary key,
	Code nvarchar(10) not null
)
use Workflow
go
create table Bugs
(
	Id int identity(1,1) primary key,
	WorkParentId int,
	PName nvarchar(50),
	MName nvarchar(50),
	QuestionType nvarchar(20),
	QuestionDescription nvarchar(50),
	CompleteDay int,
	MoldPerson nvarchar(10),
	PlanDescription nvarchar(max),
	CreateTime datetime default(getdate())
)

use Workflow
go
create table PJMains
(
	Id int identity(1,1)  primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	PJId nvarchar(10),
	PJName nvarchar(50),
	Mold nvarchar(50),
	Maktx nvarchar(50),
	MaktxName nvarchar(50),
	Customer nvarchar(50),
	BusinessPlate nvarchar(50),
	PM nvarchar(50),
	StartDate datetime,
	PJType nvarchar(50),
	MoldType nvarchar(50),
	T1EndDate datetime,
	T2EndDate datetime,
	T3EndDate datetime,
	T4EndDate datetime,
	T5EndDate datetime,
	Statues nvarchar(1) default(0)
)

use Workflow
go
create table PJMainsTemp
(
	Id int identity(1,1)  primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	PJId nvarchar(10),
	PJName nvarchar(50),
	Mold nvarchar(50),
	Maktx nvarchar(50),
	MaktxName nvarchar(50),
	Customer nvarchar(50),
	BusinessPlate nvarchar(50),
	PM nvarchar(50),
	StartDate datetime,
	PJType nvarchar(50),
	MoldType nvarchar(50),
	T1EndDate datetime,
	T2EndDate datetime,
	T3EndDate datetime,
	T4EndDate datetime,
	T5EndDate datetime,
	Statues nvarchar(1) default(0)
)
use Workflow
go
create table PJLogs
(
	Id int identity(1,1)  primary key,
	PJId nvarchar(10),
	Stage nvarchar(10),
	Person nvarchar(10),
	CompleteDate datetime,
	LogContent nvarchar(max),
	Statues nvarchar(50)
)
use Workflow
go
create table PJNodeMoulds
(
	Id int identity(1,1) primary key,
	Number int,
	NodeName nvarchar(50),
)
go
create table PJNodes
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	PJId nvarchar(10),
	Number int,
	NodeName nvarchar(50),
	CompleteDate datetime,
	Stautes nvarchar(1) default(0)
)
go
create table PJNodesTemp
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	PJId nvarchar(10),
	Number int,
	NodeName nvarchar(50),
	CompleteDate datetime,
	Stautes nvarchar(1) default(0)
)
use Workflow
go
create table PJMainsDateTemp
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	PJId nvarchar(10),
	StageName nvarchar(10),
	ReaEndDate datetime
)
use Workflow
go
create table PJCustomerFrees
(
	Id int identity(1,1) primary key,
	FSId nvarchar(10),
	OrderNum int,
	Mold nvarchar(10),
	Machine nvarchar(max),
	Customer nvarchar(50),
	Maktx nvarchar(50),
	Reason nvarchar(max),
	Requirement nvarchar(max),
	Department nvarchar(max)
)

use Workflow
go
create table PJOrderStarts
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	SerialNum nvarchar(8),
	OrderType nvarchar(50),
	OrderPurpose nvarchar(50)
)
go
create table PJOrderChildStarts
(
	Id int identity(1,1) primary key,
	SerialNum nvarchar(8),
	CustOrDepart nvarchar(50),
	CustomerOrder nvarchar(20),
	Maktx nvarchar(50),
	Number int,
	Mold nvarchar(10),
	ConfirmDate date,
	SapOrder nvarchar(20),
	Ps nvarchar(max)
)

use workflow
go
create table PJSampleOuts
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	SerialNum nvarchar(8),
	OrderType nvarchar(10),
	OrderStartSerialNum nvarchar(8),
	OutDate date,
	Customer nvarchar(50),
	CustomerAddress nvarchar(max),
	Person nvarchar(50),
	SaleOrder nvarchar(50),
	Bill nvarchar(50),
	Statues nvarchar(1)
)
use workflow
go
create table PJSampleChildOuts
(
	Id int identity(1,1) primary key,
	SerialNum nvarchar(8),
	CustomerOrder nvarchar(50),
	Maktx nvarchar(50),
	[Description] nvarchar(50),
	Num int,
	Rev nvarchar(10),
	Ps nvarchar(max)
)
use workflow
go
create table PJSampleIns
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	SerialNum nvarchar(8),
	OrderType nvarchar(50),
	OrderPurpose nvarchar(50),
	BusinessReason nvarchar(50),
	Maktx nvarchar(50),
	Num int,
	SapBill nvarchar(50),
	Statues nvarchar(1)
)
use workflow
go
create table PJLineDebug
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	SerialNum nvarchar(8),
	OrderType nvarchar(50),
	LineNum nvarchar(50),
	OrderStartSerialNum nvarchar(8),
	SapAsset nvarchar(50),
	SapBill nvarchar(50),
	Statues nvarchar(1)
)
use workflow
CREATE TABLE [dbo].[PJLogsTemp] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [WorkParentId]     INT            NULL,
    [PJId]             NVARCHAR (10)  NULL,
    [Stage]            NVARCHAR (10)  NULL,
    [Node]             NVARCHAR (10)  NULL,
    [Problem]          NVARCHAR (MAX) NULL,
    [Source]           NVARCHAR (50)  NULL,
    [Person]           NVARCHAR (50)  NULL,
    [Department]       NVARCHAR (20)  NULL,
    [Statues1]         NVARCHAR (10)  NULL,
    [Degree]           NVARCHAR (10)  NULL,
    [StartDate]        DATETIME       NULL,
    [CompleteDate]     DATETIME       NULL,
    [RealCompleteDate] DATETIME       NULL,
    [Ps]               NVARCHAR (MAX) NULL,
    [Statues]          NVARCHAR (1)   DEFAULT ((0)) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);